package com.wu.pojo.bean;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.wu.utils.CommonUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@Slf4j
@SuppressWarnings("all")
public class ReadExcel  implements Serializable {

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @param
     * @return
     */
    public Map<String, Object> getExcelInfo(byte[] pic, String name) {
        List<ExcelBean> areaList = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        try {
            if (!validateExcel(name)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(name)) {
                isExcel2003 = false;
            }
            map = createExcel(new ByteArrayInputStream(pic), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is          输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public Map<String, Object> createExcel(InputStream is, boolean isExcel2003) throws Exception {
        List<ExcelBean> areaList = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        try {
            Workbook wb = null;

            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            String sheetName = wb.getSheetName(0);// 获取sheet名以gong供校验
            map.put("sheetName",sheetName);
            areaList = readExcelValue(wb);//读取Excel里面客户的信息
            map.put("cellList",areaList);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                is.close();
            }
        }
        return map;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb
     * @return
     */
    private List<ExcelBean> readExcelValue(Workbook wb) throws Exception {
        List<ExcelBean> list = new ArrayList<ExcelBean>();

//        // excel总的sheet个数
//        int sheetCount = wb.getNumberOfSheets();
//        sheetCount = isFirstSheet ? 1 : sheetCount;// 首页则取第一个sheet页
        Sheet sheet = null;
        // 得到第一个shell
        sheet = wb.getSheetAt(0);
        Row row = null;
        String cellValue = null;// 单元格的值
        int col = -1;// 计算列

        // 解析标题行
        Row titleRow = sheet.getRow(0);
        // 第二列-题型
        if (!"题型".equals(String.valueOf(titleRow.getCell(1)))) {
            log.error("第{}列标题不匹配","2");
            throw new RuntimeException("请下载最新模板");
        }
        // 第三列-题型名称
        if (!"题型名称".equals(String.valueOf(titleRow.getCell(2)))) {
            log.error("第{}列标题不匹配","3");
            throw new RuntimeException("请下载最新模板");
        }
        // 第四列-问题标题
        if (!"问题标题".equals(String.valueOf(titleRow.getCell(3)))) {
            log.error("第{}列标题不匹配","4");
            throw new RuntimeException("请下载最新模板");
        }
        // 第五列-选项
        if (!"选项".equals(String.valueOf(titleRow.getCell(4)))) {
            log.error("第{}列标题不匹配","5");
            throw new RuntimeException("请下载最新模板");
        }
        // 第六列-包含答案选择四个字
        if (!String.valueOf(titleRow.getCell(5)).contains("答案选择")) {
            log.error("第{}列标题不匹配","6");
            throw new RuntimeException("请下载最新模板");
        }

        // 得到Excel的行数
        int totalRows = sheet.getLastRowNum();

        if (totalRows == 0) {
            throw new Exception("请填写行数");
        }

        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        String paperId = CommonUtils.getUUID();

        for (int j = 1; j < totalRows + 1; j++) {
            //读取左上端单元格
            row = sheet.getRow(j);
            //行不为空
            if (row != null) {
                //读取cell
                ExcelBean excelBean = new ExcelBean();
                excelBean.setPaperId(paperId);
                //题号
                String questionNo = getCellValue(row.getCell(0));
                excelBean.setQuestionNo(questionNo);
                //题型
                Integer questionType = Integer.valueOf(getCellValue(row.getCell(1)));
                excelBean.setQuestionType(questionType);
                //题型名称
                String typeName = getCellValue(row.getCell(2));
                excelBean.setTypeName(typeName);
                //问题标题
                String question = getCellValue(row.getCell(3));
                excelBean.setQuestion(question);
                //选项
                String option = getCellValue(row.getCell(4));
                excelBean.setAnswer(option);
                //答案选择
                String answer = getCellValue(row.getCell(5));
                excelBean.setAnswer(answer);
                list.add(excelBean);//把实数据放入集合里
            }
        }
        return list;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            String errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // 判断是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //判断是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    //获取Cell内容
    private static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            //以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC://数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: //字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: //boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: //公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: //空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: //故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }
}
